package com.yellowpage.ndc.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.yellowpage.ndc.dao.ProdClassDao;
import com.yellowpage.ndc.data.ProdClass;
import com.yellowpage.white.common.Common;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.white.util.SqlUtil;

public class ProdClassServiceImpl extends BaseServiceImpl implements ProdClassService {
	  private ProdClassDao prodClassDao;
	
	  public void setProdClassDao(ProdClassDao prodClassDao) {
		  this.prodClassDao = prodClassDao;
	  }
	
	
	  /* Smooth generate Method */ 
	  public ProdClass getProdClass(Long id){
	      return prodClassDao.get(id);
	  }
	  
    public List<ProdClass> getAllProdClass(){
        return prodClassDao.getAll();
    }
    
    public List<Map> queryProdClassByPage(Map queryMap, PageRender pageRender) {		
//    	String prodCodeSql = "";
    	
//		if(type.equals("1")){
//			prodCodeSql = " and CODE LIKE '5%' ";
//		}else if(type.equals("2")){
//		}

		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				" SELECT pc.CLASS_ID,pc.CITY_CODE,pc.CITY_NAME,pc.CODE,pc.LINE,pc.NAME,pc.ENG_NAME ,"
				+ "(SELECT COUNT(1) FROM db_ndc.PROD_CUSTOMER WHERE PROD_CLASS_CODE2= pc.code) as countcustomer,"
				+ "(SELECT COUNT(1) FROM db_ndc.PROD_GROUP WHERE PROD_CLASS_CODE2= pc.code) as countgroup   " +
				" FROM db_ndc.PROD_CLASS pc " +
				" WHERE 1=1 ")
				    			.addCondition(" and LINE LIKE :line ")
							    			.addCondition(" and CLASS_ID = :classId ")
							    			.addCondition(" and CITY_CODE = :cityCode ")
							    			.addCondition(" and CITY_NAME = :cityName ")
							    			.addCondition(" and ENG_NAME = :engName ")
							    			.addCondition(" and PY_NAME = :pyName ")
							    			.addCondition(" and BX_NAME = :bxName ")
							    			.addCondition(" and NOTE = :note ")
							    			.addCondition(" and NAME = :name ")
							    			.addCondition(" and TYPE = :type ")
							    			.addCondition(" and STATUS = :status ")
							    			.addCondition(" and CODE LIKE :code ");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}
    
    public List<Map> ProdClassexcel(Map queryMap) {		


		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				" SELECT pc.CLASS_ID,pc.CITY_CODE,pc.CITY_NAME,pc.CODE,pc.LINE,pc.NAME,pc.ENG_NAME ,"
				+ "(SELECT COUNT(1) FROM db_ndc.PROD_CUSTOMER WHERE PROD_CLASS_CODE2= pc.code) as countcustomer,"
				+ "(SELECT COUNT(1) FROM db_ndc.PROD_GROUP WHERE PROD_CLASS_CODE2= pc.code) as countgroup   " +
				" FROM db_ndc.PROD_CLASS pc " +
				" WHERE 1=1 ")
				    			.addCondition(" and LINE LIKE :line ")
							    			.addCondition(" and CLASS_ID = :classId ")
							    			.addCondition(" and CITY_CODE = :cityCode ")
							    			.addCondition(" and CITY_NAME = :cityName ")
							    			.addCondition(" and ENG_NAME = :engName ")
							    			.addCondition(" and PY_NAME = :pyName ")
							    			.addCondition(" and BX_NAME = :bxName ")
							    			.addCondition(" and NOTE = :note ")
							    			.addCondition(" and NAME = :name ")
							    			.addCondition(" and TYPE = :type ")
							    			.addCondition(" and STATUS = :status ")
							    			.addCondition(" and CODE LIKE :code ");

		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
	}
    
    public List<Map> queryProdClassSelByPage(Map queryMap, PageRender pageRender, String type) {
    	log.error("queryProdClassSelByPage in....................");
    	
    	StringBuffer code1WhereSql = new StringBuffer();
    	StringBuffer code2WhereSql = new StringBuffer();
    	
    	if (Common.isValid(queryMap.get("code1"))) {
    		code1WhereSql.append(SqlUtil.parseInputToSQL((String) queryMap.get("code1"),"pc1.CODE",SqlUtil.PROPERTY_TYPE_STRING));
    	}
    	if (Common.isValid(queryMap.get("name1"))) {
    		code1WhereSql.append(SqlUtil.parseInputToSQL((String) queryMap.get("name1"),"pc1.NAME",SqlUtil.PROPERTY_TYPE_STRING));
    	}
    	if (Common.isValid(queryMap.get("code2"))) {
    		code2WhereSql.append(SqlUtil.parseInputToSQL((String) queryMap.get("code2"),"pc2.CODE",SqlUtil.PROPERTY_TYPE_STRING));
    	}
    	if (Common.isValid(queryMap.get("name2"))) {
    		code2WhereSql.append(SqlUtil.parseInputToSQL((String) queryMap.get("name2"),"pc2.NAME",SqlUtil.PROPERTY_TYPE_STRING));
    	}
    	
    	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select prodCode1.CITY_CODE,prodCode1.CITY_NAME,prodCode1.CODE as CODE1,prodCode1.NAME as NAME1,prodCode2.CODE as CODE2,prodCode2.NAME as NAME2,prodCode1.LINE " +
    			                                                      "   from (select pc1.CLASS_ID,pc1.CITY_CODE,pc1.CITY_NAME,pc1.CODE,pc1.NAME,left(pc1.LINE,2) as LINE " + 
    			                                                      "           from db_ndc.PROD_CLASS pc1 " + 
    			                                                      "          where (pc1.CITY_CODE = '000000' or pc1.CITY_CODE is NULL) " + 
    			                                                      "            and right(pc1.LINE,10) = '0000000000' " +
    			                                                      "            and pc1.LINE <> '000000000000' " + 
    			                                                      code1WhereSql +
    			                                                      " ) prodCode1, " + 
    			                                                      "        (select pc2.CLASS_ID,pc2.CITY_CODE,pc2.CITY_NAME,pc2.CODE,pc2.NAME,left(pc2.LINE,2) as LINE " +
    			                                                      "           from db_ndc.PROD_CLASS pc2 " +
    			                                                      "          where (pc2.CITY_CODE = '000000' or pc2.CITY_CODE is NULL) " +
    			                                                      "            and right(pc2.LINE,10) <> '0000000000' " + 
    			                                                      "            and pc2.LINE <> '000000000000' " +
    			                                                      code2WhereSql +
    			                                                      " ) prodCode2 " +
    			                                                      "  where prodCode1.LINE = prodCode2.LINE");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
    }
    
    
    public List<Map> getProdClassTree(Map queryMap){
    	QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select prodCode1.CLASS_ID,prodCode2.CLASS_ID, prodCode1.CITY_CODE,prodCode1.CITY_NAME,prodCode1.CODE as CODE1,prodCode1.NAME as NAME1,prodCode2.CODE as CODE2,prodCode2.NAME as NAME2,prodCode1.LINE " +
                "   from (select pc1.CLASS_ID,pc1.CITY_CODE,pc1.CITY_NAME,pc1.CODE,pc1.NAME,left(pc1.LINE,2) as LINE " + 
                "           from db_ndc.PROD_CLASS pc1 " + 
                "          where (pc1.CITY_CODE = '000000' or pc1.CITY_CODE is NULL) " + 
                "            and right(pc1.LINE,10) = '0000000000' " +
                "            and pc1.LINE <> '000000000000' " + 
                " ) prodCode1, " + 
                "        (select pc2.CLASS_ID,pc2.CITY_CODE,pc2.CITY_NAME,pc2.CODE,pc2.NAME,left(pc2.LINE,2) as LINE " +
                "           from db_ndc.PROD_CLASS pc2 " +
                "          where (pc2.CITY_CODE = '000000' or pc2.CITY_CODE is NULL) " +
                "            and right(pc2.LINE,10) <> '0000000000' " + 
                "            and pc2.LINE <> '000000000000' " +

                " ) prodCode2 " +
                "  where prodCode1.LINE = prodCode2.LINE")
               .addCondition(" and prodCode1.CODE LIKE :code1 ")
               .addCondition(" and prodCode2.CODE LIKE :code2 ")
                ;

			List<Map> list = sqlAssistant.query(
			baseDao.getConnection(),queryMap);
			
			List<Map> fclass = new ArrayList<Map>();
			List<Map> cclass = new ArrayList<Map>();
			
			List<Map> fristClassList = new ArrayList<Map>();
			Map fristMap = new HashMap();
			
			for (Map map : list) {

				String key = map.get("CODE1").toString();
				String classId = map.get("CLASS_ID").toString();
				String name = map.get("NAME1").toString();
				if(fristMap.get(key) == null){
					fristMap.put(key, key+"&"+classId+"&"+name);
					fristClassList.add(fristMap);
				}
				
					
			}

			
			List<Map> listmapend = new ArrayList<Map>();
			String strpar = fristClassList.get(0).toString();
			strpar = strpar.replace("{","");
			strpar = strpar.replace("}","");
			String[] strClass = strpar.split(",");
			

			
			for (int n = 0; n<strClass.length; n++ ) {
				
				String value = strClass[n];

				String str = value.substring(value.indexOf("="));
				str = str.replace("=","");
				
				String[] classOne=str.split("&");
				String code = classOne[0];
				List<Map> centClassList = new ArrayList<Map>();
				
				for (Map val : list) {
					
					if(val.get("CODE1").equals(code)){
						centClassList.add(val);
					}
					
				}
				
				Map endMap = new HashMap();
				endMap.put("code", classOne[0]);
				endMap.put("classid", classOne[1]);
				endMap.put("name", classOne[2]);
				endMap.put("scendClass", centClassList);
				
				listmapend.add(endMap);
				
			}
			return listmapend;
			    	

    }
    
    
    
    public void saveProdClass(ProdClass prodClass){
        prodClassDao.save(prodClass);
    }
    
	  public void updateProdClass(ProdClass prodClass){
	      prodClassDao.update(prodClass);
	  }
	  
	  public void delete(List deleteList){
	      prodClassDao.remove(deleteList);
	  }
	  public String getCodeName(String code){
		 QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(" select NAME from db_ndc.PROD_CLASS where CODE ='"+code+"' "); 	
	        
		 List<Map> resultList = sqlAssistant.query(baseDao.getConnection(), new HashMap());
		  if(resultList.size()>0){
			  return resultList.get(0).get("NAME").toString();
		  }else{
			  return null;
		  }
	  }
	  
	 
	
}	